--region [dbo].[AddCurrentTreatment]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[AddCurrentTreatment]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[AddCurrentTreatment]') IS NOT NULL
	DROP PROCEDURE [dbo].[AddCurrentTreatment]
GO

CREATE PROCEDURE [dbo].[AddCurrentTreatment]
	@PRId int,
	@TreatmentId int,
	@IsUsing int,
	@DataValue float,
	@TreatmentHistoryId int
AS

INSERT INTO [PatientCurrentTreatment] (
	[PRId],
	[TreatmentId],
	[IsUsing],
	[DataValue],
	[TreatmentHistoryId]
) VALUES (
	@PRId,
	@TreatmentId,
	@IsUsing,
	@DataValue,
	@TreatmentHistoryId
)

GO
--endregion


--region [dbo].[UpdateCurrentTreatment]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[UpdateCurrentTreatment]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[UpdateCurrentTreatment]') IS NOT NULL
	DROP PROCEDURE [dbo].[UpdateCurrentTreatment]
GO

CREATE PROCEDURE [dbo].[UpdateCurrentTreatment]
	@PRId int,
	@TreatmentId int,
	@IsUsing int,
	@DataValue float,
	@TreatmentHistoryId int
AS

UPDATE [PatientCurrentTreatment] SET
	[IsUsing] = @IsUsing,
	[DataValue] = @DataValue,
	[TreatmentHistoryId] = @TreatmentHistoryId
WHERE
	[PRId] = @PRId AND
	[TreatmentId] = @TreatmentId

GO
--endregion


--region [dbo].[UpdateCurrentTreatmentStatus]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[UpdateCurrentTreatmentStatus]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[UpdateCurrentTreatmentStatus]') IS NOT NULL
	DROP PROCEDURE [dbo].[UpdateCurrentTreatmentStatus]
GO

CREATE PROCEDURE [dbo].[UpdateCurrentTreatmentStatus]
	@PRId int,
	@TreatmentId int,
	@IsUsing int
AS

UPDATE [PatientCurrentTreatment] SET
	[IsUsing] = @IsUsing
WHERE
	[PRId] = @PRId AND
	[TreatmentId] = @TreatmentId

GO
--endregion


--region [dbo].[DelCurrentTreatment]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[DelCurrentTreatment]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[DelCurrentTreatment]') IS NOT NULL
	DROP PROCEDURE [dbo].[DelCurrentTreatment]
GO

CREATE PROCEDURE [dbo].[DelCurrentTreatment]
	@WhereCondition nvarchar(512)
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
DELETE FROM
	[PatientCurrentTreatment]
WHERE
	' + @WhereCondition

EXEC sp_executesql @SQL

GO
--endregion


--region [dbo].[GetCurrentTreatment]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetCurrentTreatment]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetCurrentTreatment]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetCurrentTreatment]
GO

CREATE PROCEDURE [dbo].[GetCurrentTreatment]
	@WhereCondition nvarchar(512)
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT
	''Id'' = [PatientTreatmentHistory].[Id],
	''PRId'' = [PatientCurrentTreatment].[PRId],
	''TreatmentId'' = [PatientCurrentTreatment].[TreatmentId],
	[IsUsing],
	''DataValue'' = [PatientCurrentTreatment].[DataValue],
	[PatientTreatmentHistory].[Frequency],
	[State],
	[StopReason],
	[UsePurPose],
	[Privacy],
	[StartDate],
	[EndDate],
	[UpdateDate]
FROM
	[PatientCurrentTreatment], [PatientTreatmentHistory]
WHERE
	[PatientCurrentTreatment].[TreatmentHistoryId] = [PatientTreatmentHistory].[Id] AND
	' + @WhereCondition

EXEC sp_executesql @SQL

GO
--endregion


--region [dbo].[GetCurrentTreatmentList]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetCurrentTreatmentList]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetCurrentTreatmentList]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetCurrentTreatmentList]
GO

CREATE PROCEDURE [dbo].[GetCurrentTreatmentList]
	@WhereCondition nvarchar(512)
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT 
	''Id'' = [PatientTreatmentHistory].[Id],
	''PRId'' = [PatientCurrentTreatment].[PRId],
	''TreatmentId'' = [PatientCurrentTreatment].[TreatmentId],
	[TreatmentName],
	''DataValue'' = [PatientCurrentTreatment].[DataValue],
	''Unit'' = [Measurementunits],
	[PatientTreatmentHistory].[Frequency],
	[State],
	[StopReason],
	[UsePurPose],
	[Privacy],
	[StartDate],
	[EndDate],
	[UpdateDate]
FROM
	[PatientCurrentTreatment], [PatientTreatmentHistory], [Treatment]
WHERE
	[PatientCurrentTreatment].[TreatmentHistoryId] = [PatientTreatmentHistory].[Id] AND
	[PatientCurrentTreatment].[TreatmentId] = [Treatment].[Id] AND
	' + @WhereCondition

EXEC sp_executesql @SQL

GO
--endregion


--region [dbo].[GetCurrentTreatmentCount]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetCurrentTreatmentCount]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetCurrentTreatmentCount]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetCurrentTreatmentCount]
GO

CREATE PROCEDURE [dbo].[GetCurrentTreatmentCount]
	@WhereCondition nvarchar(512)
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT 
	COUNT(Id)
FROM
	[PatientCurrentTreatment]
WHERE
	' + @WhereCondition

EXEC sp_executesql @SQL

GO
--endregion



--region [dbo].[AddTreatmentHistory]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[AddTreatmentHistory]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[AddTreatmentHistory]') IS NOT NULL
	DROP PROCEDURE [dbo].[AddTreatmentHistory]
GO

CREATE PROCEDURE [dbo].[AddTreatmentHistory]
	@PRId int,
	@TreatmentId int,
	@DataValue float,
	@Frequency nvarchar(50),
	@State int,
	@StartDate datetime,
	@EndDate datetime,
	@StopReason int,
	@UsePurPose int,
	@UpdateDate datetime,
	@Privacy int,
	@id int OUTPUT
AS

INSERT INTO [PatientTreatmentHistory] (
	[PRId],
	[TreatmentId],
	[DataValue],
	[Frequency],
	[state],
	[StartDate],
	[EndDate],
	[StopReason],
	[UsePurpose],
	[UpdateDate],
	[Privacy]
) VALUES (
	@PRId,
	@TreatmentId,
	@DataValue, 
	@Frequency,
	@State, 
	@StartDate, 
	@EndDate,
	@StopReason,
	@UsePurPose,
	@UpdateDate,
	@Privacy
)

SET @id = SCOPE_IDENTITY()

GO
--endregion


--region [dbo].[UpdateTreatmentHistory]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[UpdateTreatmentHistory]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[UpdateTreatmentHistory]') IS NOT NULL
	DROP PROCEDURE [dbo].[UpdateTreatmentHistory]
GO

CREATE PROCEDURE [dbo].[UpdateTreatmentHistory]
	@Id int,
	@PRId int,
	@TreatmentId int,
	@DataValue float,
	@Frequency nvarchar(50),
	@State int,
	@StartDate datetime,
	@EndDate datetime,
	@StopReason int,
	@UsePurPose int,
	@UpdateDate datetime,
	@Privacy int
AS

UPDATE [PatientTreatmentHistory] SET
	[DataValue] = @DataValue,
	[Frequency] = @Frequency,
	[State] = @State,
	[StartDate] = @StartDate,
	[EndDate] = @EndDate,
	[StopReason] = @StopReason,
	[UsePurPose] = @UsePurPose,
	[UpdateDate] = @UpdateDate,
	[Privacy] = @Privacy
WHERE
	[PRId] = @PRId AND [TreatmentId] = @TreatmentId AND [Id] = @Id 
GO
--endregion


--region [dbo].[StopCurrentTreatment]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[StopCurrentTreatment]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[StopCurrentTreatment]') IS NOT NULL
	DROP PROCEDURE [dbo].[StopCurrentTreatment]
GO

CREATE PROCEDURE [dbo].[StopCurrentTreatment]
	@Id int,
	@PRId int,
	@TreatmentId int,
	@State int,
	@EndDate datetime,
	@StopReason int,
	@UpdateDate datetime
AS

UPDATE [PatientTreatmentHistory] SET
	[State] = @State,
	[EndDate] = @EndDate,
	[StopReason] = @StopReason,
	[UpdateDate] = @UpdateDate
WHERE
	[PRId] = @PRId AND [TreatmentId] = @TreatmentId AND [Id] = @Id 
GO
--endregion


--region [dbo].[DelTreatmentHistory]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[DelTreatmentHistory]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[DelTreatmentHistory]') IS NOT NULL
	DROP PROCEDURE [dbo].[DelTreatmentHistory]
GO

CREATE PROCEDURE [dbo].[DelTreatmentHistory]
	@WhereCondition nvarchar(512)
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
DELETE FROM
	[PatientTreatmentHistory]
WHERE
	' + @WhereCondition

EXEC sp_executesql @SQL

GO
--endregion


--region [dbo].[GetTreatmentHistory]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetTreatmentHistory]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetTreatmentHistory]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetTreatmentHistory]
GO

CREATE PROCEDURE [dbo].[GetTreatmentHistory]
	@WhereCondition nvarchar(512)
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT
	''Id'' = [PatientTreatmentHistory].[Id],
	[PRId],
	[TreatmentId],
	[TreatmentName],
	[DataValue],
	[PatientTreatmentHistory].[Frequency],
	[State],
	[StartDate],
	[EndDate],
	[StopReason],
	[UsePurPose],
	[UpdateDate],
	[Privacy]
FROM
	[PatientTreatmentHistory], [Treatment]
WHERE
	[PatientTreatmentHistory].[TreatmentId] = [Treatment].[Id] AND
	' + @WhereCondition

EXEC sp_executesql @SQL

GO
--endregion


--region [dbo].[GetTreatmentHistoryList]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetTreatmentHistoryList]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetTreatmentHistoryList]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetTreatmentHistoryList]
GO

CREATE PROCEDURE [dbo].[GetTreatmentHistoryList]
	@PageSize int,
	@PageIndex int,
	@WhereCondition nvarchar(512),
	@OrderByExpression nvarchar(250) = NULL
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT 
	COUNT(Id) 
FROM [PatientTreatmentHistory]
	'
IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + ' WHERE ' + @WhereCondition
END 

EXEC sp_executesql @SQL

SET @SQL = '
SELECT *
FROM
	( SELECT 
	''Id'' = [PatientTreatmentHistory].[Id],
	[PRId],
	[TreatmentId],
	[TreatmentName],
	[DataValue],
	''Unit'' = [Measurementunits],
	[PatientTreatmentHistory].[Frequency],
	[State],
	[StartDate],
	[EndDate],
	[StopReason],
	[UsePurPose],
	[UpdateDate],
	ROW_NUMBER() OVER ( ORDER BY ' + @OrderByExpression + ' ) AS SerialNumber
FROM
	[PatientTreatmentHistory], [Treatment]
WHERE
	[PatientTreatmentHistory].[TreatmentId] = [Treatment].[Id]	
	'

IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + ' AND ' + @WhereCondition
END

SET @SQL = @SQL + ') AS tblTmp  
	WHERE tblTmp.SerialNumber > '
	+ STR((@PageIndex-1)*@PageSize) + ' AND 
	tblTmp.SerialNumber <= '
	+ STR(@PageIndex*@PageSize)

EXEC sp_executesql @SQL

GO
--endregion


--region [dbo].[GetTreatmentHistoryCount]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetTreatmentHistoryCount]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetTreatmentHistoryCount]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetTreatmentHistoryCount]
GO

CREATE PROCEDURE [dbo].[GetTreatmentHistoryCount]
	@WhereCondition nvarchar(512)
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT 
	COUNT(Id)
FROM
	[PatientTreatmentHistory]
WHERE
	' + @WhereCondition

EXEC sp_executesql @SQL

GO
--endregion


--region [dbo].[GetAllTreatmentHistory]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetAllTreatmentHistory]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetAllTreatmentHistory]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetAllTreatmentHistory]
GO

CREATE PROCEDURE [dbo].[GetAllTreatmentHistory]
	@WhereCondition nvarchar(512),
	@OrderByExpression nvarchar(250) = NULL
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT 
	''Id'' = [PatientTreatmentHistory].[Id],
	[PRId],
	[TreatmentId],
	[TreatmentTypeId],
	[TreatmentName],
	[DataValue],
	''Unit'' = [Measurementunits],
	[PatientTreatmentHistory].[Frequency],
	[State],
	[StartDate],
	[EndDate],
	[StopReason],
	[UsePurPose],
	[UpdateDate]
FROM
	[PatientTreatmentHistory], [Treatment]
WHERE
	[PatientTreatmentHistory].[TreatmentId] = [Treatment].[Id]
	'

IF @WhereCondition <> ''
BEGIN
SET @SQL = @SQL + ' AND ' + @WhereCondition
END

IF @OrderByExpression <> ''
BEGIN
	SET @SQL = @SQL + ' 
ORDER BY
	' + @OrderByExpression 
END	

EXEC sp_executesql @SQL

GO
--endregion